package com.xian;

import org.apache.commons.lang3.StringUtils;
import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.hive.HiveContext;

import java.io.File;
import java.sql.*;
import java.util.Scanner;

/**
 * mysql -upubuser -h172.18.5.140 -pADA1da4day8AA_A7Drrr124
 * jdbc:mysql://172.18.5.140 pubuser ADA1da4day8AA_A7Drrr124 2021-04-16
 */
public class JDBCReadMysqlData {
    public static void main(String[] args) throws Exception {
        String url = args[0];
        if(StringUtils.isBlank(url)){
            throw new IllegalArgumentException("第1个参数url文件不能为空!");
        }
        System.out.println("传入url为:"+url);

        String username = args[1];
        if(StringUtils.isBlank(username)){
            throw new IllegalArgumentException("第2个username为空!");
        }
        System.out.println("传入username为:"+username);


        String password = args[2];
        if(StringUtils.isBlank(password)){
            throw new IllegalArgumentException("第3个password为空!");
        }
        System.out.println("传入password为:"+password);

        String exeDate = args[3];
        if(StringUtils.isBlank(exeDate)){
            throw new IllegalArgumentException("第4日期不能为空!");
        }
        System.out.println("传入日期为:"+exeDate);


        //获取建表语句
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection(url,username,password);
        Statement st = con.createStatement();





        //遍历sql文件执行每一行数据
        Scanner scanner = new Scanner(new File("data/my.sql"));
        String sql = null;
        while (scanner.hasNextLine()) {
            sql  = scanner.nextLine();
            System.out.println("读取原SQL:"+sql);
            sql = sql.replace("${exeDate}",exeDate);
            System.out.println("替换日期参数后执行SQL:"+sql);
            ResultSet rs = st.executeQuery(sql);


            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            //读取每一行数据
            StringBuilder lineBuf = new StringBuilder();
            while (rs.next()) {
                //读取某一行的每一列数据
                for(int c=1; c<=columnCount; c++){

                    Object obj = rs.getObject(c);
                    lineBuf.append(obj).append(",");
                }
                System.out.println(lineBuf);
                lineBuf.delete(0,lineBuf.length()); //清空StringBuilder
            }


        }


    }
}
